//
//  DataBaseSingleton.m
//  DaHaiDouBan
//
//  Created by dllo on 15/11/3.
//  Copyright © 2015年 Wang Dahai. All rights reserved.
//

#import "DataBaseSingleton.h"
#import <sqlite3.h>
@implementation DataBaseSingleton

+(instancetype)shareDataBase
{
    static DataBaseSingleton *dataBase = nil;
    
    if (dataBase == nil) {
        
        dataBase = [[DataBaseSingleton alloc] init];
        
    }
    
    return dataBase;
    
}
//声明数据库对象
static sqlite3 *db = nil;
//打开数据库
-(void)openDB
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"cinema.sqlite"];
    
    NSLog(@"=========%@",sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);
    //SQLITE_OK 对应的是0
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
        
    }else
    {
        NSLog(@"打开失败");
    
    }

}
//收藏表
-(void)createCollectionTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS collectionActivity(ID INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT,begin_time TEXT,end_time TEXT,address TEXT,category_name TEXT,wisher_count INTEGER,participant_count INTEGER,image TEXT,name TEXT,content TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"成功创建收藏表");
    }
    else{
        NSLog(@"创建收藏表失败");
    }
}

-(void)insertCollectionTable:(Activity *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO collectionActivity(title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content) VALUES('%@' , '%@' , '%@' , '%@' , '%@' , '%ld' , '%ld' , '%@' , '%@' , '%@')",activity.title,activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.wisher_count,activity.participant_count,activity.image,activity.name,activity.content];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"插入收藏表成功");
    }
    else{
        NSLog(@"插入收藏表失败");
    }
    
}

-(Activity *)selectCollectionWithTitle:(NSString *)title{
    Activity *activity = [[Activity alloc] init];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM collectionActivity WHERE title = '%@'",title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询收藏准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            
            
        }
    }
    else{
        NSLog(@"查询收藏准备失败");
    }
    
    
    return activity;
}

-(NSMutableArray *)selectAllCollection{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM collectionActivity";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询收藏准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            Activity *activity = [[Activity alloc] init];
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            
            [array addObject:activity];
            
            [activity release];
        }
    }
    else{
        NSLog(@"查询收藏准备失败");
    }
    
    return array;
}

-(void)dropCollectionTable{
    NSString *sql = @"DROP TABLE collectionActivity";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"删除收藏表成功");
    }
    else{
        NSLog(@"删除收藏表失败");
    }
    
}



//  缓存表

-(void)createActivityTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activity(ID INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT,begin_time TEXT,end_time TEXT,address TEXT,category_name TEXT,wisher_count INTEGER,participant_count INTEGER,image TEXT,name TEXT,content TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"成功创建缓存表");
    }
    else{
        NSLog(@"创建缓存表失败");
    }
    
}

-(void)insertActivityTable:(Activity *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activity(title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content) VALUES('%@' , '%@' , '%@' , '%@' , '%@' , '%ld' , '%ld' , '%@' , '%@' , '%@')",activity.title,activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.wisher_count,activity.participant_count,activity.image,activity.name,activity.content];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"插入缓存表成功");
    }
    else{
        NSLog(@"插入缓存表失败");
    }
    
}

-(void)dropActivityTable{
    NSString *sql = @"DROP TABLE activity";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"删除缓存表成功");
    }
    else{
        NSLog(@"删除缓存表失败");
    }
    
}

-(NSMutableArray *)selectAllActivity{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM activity";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            Activity *activity = [[Activity alloc] init];
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            
            [array addObject:activity];
            
            [activity release];
        }
    }
    else{
        NSLog(@"查询缓存准备失败");
    }
    
    return array;
    
}

-(Activity *)selectActivityWithTitle:(NSString *)title
{
    Activity *activity = [[Activity alloc] init];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activity WHERE title = '%@'",title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
        }
    }
    else{
        NSLog(@"查询缓存准备失败");
    }
    
    
    return activity;
    
}














//缓存表
-(void)createTable
{
    //建表语句
    NSString *sql = @"CREATE TABLE IF NOT EXISTS cinema(number INTEGER PRIMARY KEY AUTOINCREMENT, cinemaName TEXT , address TEXT ,telephone TEXT)";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"创建表成功");
        
    } else
    {
        NSLog(@"创建表失败");
    }
}
-(void)insertTabel:(Cinema *)cinema
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO cinema(cinemaName,address,telephone) VALUES ('%@', '%@', '%@')",cinema.cinemaName,cinema.address,cinema.telephone];

    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"插入缓存表成功");
    
    }else
    {
        
        NSLog(@"插入缓存表失败");
    }
    
}
-(void)dropTable
{
    
    NSString *sql = @"DROP TABLE cinema";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除缓存表成功");
        
        
    }else
    {
        NSLog(@"删除缓存表失败");
    }
    
    
}
-(NSMutableArray *)selectAllCinema
{
    
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM cinema";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        int number = sqlite3_column_int(stmt, 0);
        
        const unsigned char *cinemaName = sqlite3_column_text(stmt, 1);
        const unsigned char *address = sqlite3_column_text(stmt, 2);
        const unsigned char *telephone = sqlite3_column_text(stmt, 3);
        
        NSInteger numberInteger = number;
        NSString *cinemaNameStr = [NSString stringWithUTF8String:(const char *)cinemaName];
        NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
        NSString *telephoneStr = [NSString stringWithUTF8String:(const char *)telephone];

        Cinema *cinema = [[Cinema alloc] init];
        cinema.cinemaName = cinemaNameStr;
        cinema.address = addressStr;
        cinema.telephone = telephoneStr;
        
        [array addObject:cinema];
        
        [cinema release];
           }
      }else
        {
            NSLog(@"查询缓存准备失败");
        }
    return array;
    
}
-(Cinema *)selectCinemaWithTitle:(NSString *)cinemaName
{
    Cinema *cinema = [[Cinema alloc] init];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activity WHERE cinemaName = '%@'",cinemaName];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *cinemaName = sqlite3_column_text(stmt, 1);
            const unsigned char *address = sqlite3_column_text(stmt, 2);
            const unsigned char *telephone = sqlite3_column_text(stmt, 3);
            
            
            NSInteger numberInteger = number;
            NSString *cinemaNameStr = [NSString stringWithUTF8String:(const char *)cinemaName];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *telephoneStr = [NSString stringWithUTF8String:(const char *)telephone];
            
            cinema.cinemaName = cinemaNameStr;
            cinema.address = addressStr;
            cinema.telephone = telephoneStr;
        
        }
        
    }else
    {
        NSLog(@"查询缓存失败");
        
    }
    
    return cinema;

}

@end
